// SQLite数据库管理模块
const fs = require('fs-extra');
const path = require('path');
const { v4: uuidv4 } = require('uuid'); // 用于生成提醒ID
console.log(`<<<<< DB.JS TOP LEVEL MARKER - TIMESTAMP: ${Date.now()} >>>>>`);

let db = null;

// 辅助函数：获取指定月份的第 N 个星期几
// (与 background.js 中的版本类似，但用于首次计算)
function getNthDayOfWeekInMonthForInitial(year, month, dayOfWeek, weekNumber, hours, minutes, seconds) {
  // 忽略seconds参数，固定使用0秒
  const date = new Date(year, month, 1, hours, minutes, 0, 0);
  let occurrences = [];

  if (weekNumber === -1) { // "最后一个"
    date.setMonth(month + 1);
    date.setDate(0); // 上个月的最后一天
    const daysInMonth = date.getDate();
    for (let day = daysInMonth; day >= 1; day--) {
      date.setDate(day);
      if (date.getMonth() === month && date.getDay() === dayOfWeek) {
        // 找到了，无需再向前找
        const foundDate = new Date(date.getTime());
        foundDate.setHours(hours, minutes, 0, 0);
        return foundDate;
      }
    }
    return null; 
  } else {
    while (date.getMonth() === month) {
      if (date.getDay() === dayOfWeek) {
        occurrences.push(new Date(date.getTime()));
      }
      date.setDate(date.getDate() + 1);
    }
    if (weekNumber > 0 && occurrences.length >= weekNumber) {
      return occurrences[weekNumber - 1];
    }
    return null;
  }
}

// 初始化数据库
function initDatabase(dbPath) {
  try {
    // 确保目录存在
    const dbDir = path.dirname(dbPath);
    fs.ensureDirSync(dbDir);
    
    console.log(`[db.js] 尝试创建数据库: ${dbPath}`);
    
    // 在尝试加载模块之前记录路径信息
    console.log(`[db.js] 当前工作目录: ${process.cwd()}`);
    
    // 尝试以不同方式加载better-sqlite3模块
    let Database = null;
    try {
      Database = require('better-sqlite3');
      console.log('[db.js] 成功加载better-sqlite3模块通过require');
    } catch (err) {
      console.error('[db.js] require方式加载模块失败:', err.message);
      
      // 尝试其他方法加载模块
      try {
        const bindings = require('bindings');
        const modulePath = bindings({
          bindings: 'better_sqlite3.node',
          try: [
            ['module_root', 'build', 'Release', 'better_sqlite3'],
            ['module_root', 'build', 'Debug', 'better_sqlite3']
          ]
        });
        console.log(`[db.js] 通过bindings找到模块路径: ${modulePath}`);
        Database = require(modulePath);
      } catch (bindingErr) {
        console.error('[db.js] bindings方法加载模块失败:', bindingErr.message);
        return false;
      }
    }
    
    if (!Database) {
      console.error('[db.js] 无法加载better-sqlite3模块');
      return false;
    }

    // 创建数据库连接
    console.log(`[db.js] 创建数据库连接: ${dbPath}`);
    db = new Database(dbPath, { verbose: console.log });
    console.log(`[db.js] SQLite数据库已连接: ${dbPath}`);
    
    // 创建数据库表
    createTables();
    
    return true;
  } catch (error) {
    console.error('[db.js] 初始化SQLite数据库失败:', error);
    console.error('[db.js] 错误堆栈:', error.stack);
    
    // 检查数据库目录是否可写
    try {
      const dbDir = path.dirname(dbPath);
      const testFilePath = path.join(dbDir, 'test_write_access.tmp');
      fs.writeFileSync(testFilePath, 'test');
      fs.unlinkSync(testFilePath);
      console.log(`[db.js] 目录 ${dbDir} 可写`);
    } catch (fsError) {
      console.error(`[db.js] 目录 ${path.dirname(dbPath)} 不可写:`, fsError.message);
    }
    
    return false;
  }
}

// 创建数据库表
function createTables() {
  console.log('[db.js] createTables() function started.');
  
  // 创建待办事项表
  try {

    console.log('[db.js] Attempting to create todos table...');

    db.exec(`
      CREATE TABLE IF NOT EXISTS todos (
        id TEXT PRIMARY KEY,
        text TEXT NOT NULL,
        completed INTEGER NOT NULL DEFAULT 0,
        created_at TEXT NOT NULL,
        completed_date TEXT,
        priority INTEGER NOT NULL DEFAULT 0,
        date_key TEXT,
        source TEXT NOT NULL,
        archived INTEGER NOT NULL DEFAULT 0,
        archived_date TEXT,
        original_source TEXT
      );
    `);

    console.log('[db.js] CREATE TABLE IF NOT EXISTS todos - executed.');
  } catch (error) {
    console.error('[db.js] Error creating todos table:', error);
  }

  // 创建提醒事项表 (reminders)
  try {
    console.log('[db.js] Attempting to create reminders table...');
    db.exec(`
      CREATE TABLE IF NOT EXISTS reminders (
        id TEXT PRIMARY KEY,
        title TEXT NOT NULL,
        description TEXT,
        reminder_time TEXT NOT NULL, -- e.g., "14:30:00"
        recurrence_type TEXT NOT NULL, -- 'ONCE', 'DAILY', 'WEEKLY', 'MONTHLY_BY_DATE', 'MONTHLY_BY_DAY_OF_WEEK'
        recurrence_details TEXT, -- JSON string for specifics, e.g., {"daysOfWeek": [1,3]} or {"dayOfMonth": 15}
        start_date TEXT NOT NULL, -- YYYY-MM-DD
        next_trigger_timestamp INTEGER, -- Unix timestamp in milliseconds
        is_active INTEGER NOT NULL DEFAULT 1, -- 1 for true, 0 for false
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL
      );
    `);
    console.log('[db.js] CREATE TABLE IF NOT EXISTS reminders - executed.');
  } catch (error) {
    console.error('[db.js] Error creating reminders table:', error);
  }
  
  console.log('[db.js] createTables() function finished.');
}

// 关闭数据库
function closeDatabase() {
  if (db) {
    db.close();
    db = null;
    console.log('[db.js] SQLite数据库已关闭');
  }
}

// 检查数据库连接
function checkConnection() {
  return db !== null;
}

// 插入默认设置
function insertDefaultSettings() {
  // 不再插入默认设置到SQLite
  /*
  const defaultSettings = {
    'theme': 'dark',
    'miniMode': 'false',
    'alwaysOnTop': 'false',
    'showCloseDialog': 'true',
    'closeDialogDefaultAction': 'tray',
    'launchOnStartup': 'false'
  };
  
  const stmt = db.prepare('INSERT OR IGNORE INTO settings (key, value) VALUES (?, ?)');
  
  for (const [key, value] of Object.entries(defaultSettings)) {
    stmt.run(key, value);
  }
  */
  console.log('[db.js] 配置数据已迁移到electron-store，不再初始化SQLite设置表');
}

// 获取所有设置
function getSettings() {
  try {
    // 不再从数据库读取设置
    // const rows = db.prepare('SELECT key, value FROM settings').all();
    // const settings = {};
    // 
    // rows.forEach(row => {
    //   // 根据值的类型进行转换
    //   let value = row.value;
    //   
    //   if (value === 'true') {
    //     value = true;
    //   } else if (value === 'false') {
    //     value = false;
    //   } else if (!isNaN(value) && value.trim() !== '') {
    //     value = Number(value);
    //   }
    //   
    //   settings[row.key] = value;
    // });
    // 
    // return settings;

    console.log('[db.js] getSettings: 配置数据已迁移到electron-store，不再从SQLite获取');
    return null; // 返回null，让前端只使用electron-store的设置
  } catch (error) {
    console.error('[db.js] 获取设置失败:', error);
    return null; // 出错时也返回null
  }
}

// 更新设置
function updateSettings(settings) {
  // 不再更新数据库中的设置
  // try {
  //   const stmt = db.prepare('INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)');
  //   
  //   for (const [key, value] of Object.entries(settings)) {
  //     stmt.run(key, String(value));
  //   }
  //   
  //   return true;
  // } catch (error) {
  //   console.error('[db.js] 更新设置失败:', error);
  //   return false;
  // }
  console.log('[db.js] updateSettings: 配置数据已迁移到electron-store，不再保存到SQLite');
  return false; // 表示不通过SQLite更新设置
}

// 添加待办事项
function addTodo(todo, source = 'inbox') {
  try {
    const stmt = db.prepare(`
      INSERT INTO todos (id, text, completed, created_at, completed_date, priority, date_key, source)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `);
    
    stmt.run(
      todo.id,
      todo.text,
      todo.completed ? 1 : 0,
      todo.createdAt,
      todo.completedDate || null,
      todo.priority || 0,
      source === 'inbox' ? null : source,
      source === 'inbox' ? 'inbox' : 'date'
    );
    
    return true;
  } catch (error) {
    console.error('[db.js] 添加待办事项失败:', error);
    return false;
  }
}

// 更新待办事项
function updateTodo(todo) {
  try {
    const stmt = db.prepare(`
      UPDATE todos SET
        text = ?,
        completed = ?,
        completed_date = ?,
        priority = ?
      WHERE id = ?
    `);
    
    stmt.run(
      todo.text,
      todo.completed ? 1 : 0,
      todo.completedDate || null,
      todo.priority || 0,
      todo.id
    );
    
    return true;
  } catch (error) {
    console.error('[db.js] 更新待办事项失败:', error);
    return false;
  }
}

// 删除待办事项
function deleteTodo(todoId) {
  try {
    const stmt = db.prepare('DELETE FROM todos WHERE id = ?');
    stmt.run(todoId);
    return true;
  } catch (error) {
    console.error('[db.js] 删除待办事项失败:', error);
    return false;
  }
}

// 获取按日期分组的待办事项
function getTodosByDate() {
  try {
    console.log('[db.js] 获取按日期分组的待办事项');
    const rows = db.prepare(`
      SELECT * FROM todos 
      WHERE source = 'date' AND archived = 0
      ORDER BY created_at DESC
    `).all();
    
    // 按日期分组
    const todosByDate = {};
    for (const row of rows) {
      const dateKey = row.date_key;
      if (!todosByDate[dateKey]) {
        todosByDate[dateKey] = [];
      }
      todosByDate[dateKey].push(formatTodoFromDb(row));
    }
    
    console.log('[db.js] 获取到的日期数量:', Object.keys(todosByDate).length);
    return todosByDate;
  } catch (error) {
    console.error('[db.js] 获取按日期分组的待办事项失败:', error);
    return {};
  }
}

// 新增：获取特定日期的待办事项
function getTodosForDateKey(dateKey) {
  try {
    console.log(`[db.js] 获取日期 ${dateKey} 的待办事项`);
    const rows = db.prepare(`
      SELECT * FROM todos 
      WHERE date_key = ? AND source = 'date' AND archived = 0
      ORDER BY created_at DESC
    `).all(dateKey);
    
    console.log(`[db.js] 获取到日期 ${dateKey} 的待办事项数量: ${rows.length}`);
    return rows.map(row => formatTodoFromDb(row));
  } catch (error) {
    console.error(`[db.js] 获取日期 ${dateKey} 的待办事项失败:`, error);
    return [];
  }
}

// 添加待办事项到特定日期
function addTodoToDate(todo, dateKey) {
  try {
    const stmt = db.prepare(`
      INSERT INTO todos (id, text, completed, created_at, completed_date, priority, date_key, source)
      VALUES (?, ?, ?, ?, ?, ?, ?, 'date')
    `);
    
    stmt.run(
      todo.id,
      todo.text,
      todo.completed ? 1 : 0,
      todo.createdAt,
      todo.completedDate || null,
      todo.priority || 0,
      dateKey
    );
    
    return true;
  } catch (error) {
    console.error('[db.js] 添加待办事项到日期失败:', error);
    return false;
  }
}

// 从日期中删除待办事项
function deleteTodoFromDate(todoId, dateKey) {
  try {
    const stmt = db.prepare(`
      DELETE FROM todos WHERE id = ? AND date_key = ? AND source = 'date'
    `);
    stmt.run(todoId, dateKey);
    return true;
  } catch (error) {
    console.error('[db.js] 从日期删除待办事项失败:', error);
    return false;
  }
}

// 批量从日期删除待办事项
function deleteBatchTodosFromDate(todoIdsArray, dateKey) {
  if (!todoIdsArray || todoIdsArray.length === 0) {
    console.log('[db.js] deleteBatchTodosFromDate: ID数组为空，不执行任何操作。');
    return true; // 或者根据需要返回 false 或 0
  }
  console.log('[db.js] deleteBatchTodosFromDate: 准备删除的ID:', todoIdsArray);
  try {
    const placeholders = todoIdsArray.map(() => '?').join(',');
    const stmt = db.prepare(`DELETE FROM todos WHERE id IN (${placeholders}) AND date_key = ? AND source = 'date'`);
    const info = stmt.run(...todoIdsArray, dateKey);
    console.log(`[db.js] deleteBatchTodosFromDate: ${info.changes} 条待办事项已删除。`);
    return info.changes > 0; // 返回是否至少删除了一条记录
  } catch (error) {
    console.error('[db.js] deleteBatchTodosFromDate 失败:', error);
    return false;
  }
}

// 获取收件箱待办事项
function getInboxTodos() {
  try {
    console.log('[db.js] 获取收件箱待办事项');
    const rows = db.prepare(`
      SELECT * FROM todos WHERE source = 'inbox' AND archived = 0
      ORDER BY created_at DESC
    `).all();
    
    console.log('[db.js] 从SQLite获取到的收件箱待办事项数量:', rows.length);
    return rows.map(row => formatTodoFromDb(row));
  } catch (error) {
    console.error('[db.js] 获取收件箱待办事项失败:', error);
    return [];
  }
}

// 添加待办事项到收件箱
function addInboxTodo(todo) {
  return addTodo(todo, 'inbox');
}

// 从收件箱删除待办事项
function deleteInboxTodo(todoId) {
  try {
    const stmt = db.prepare(`
      DELETE FROM todos WHERE id = ? AND source = 'inbox'
    `);
    stmt.run(todoId);
    return true;
  } catch (error) {
    console.error('[db.js] 从收件箱删除待办事项失败:', error);
    return false;
  }
}

// 批量从收件箱删除待办事项
function deleteBatchInboxTodos(todoIdsArray) {
  if (!todoIdsArray || todoIdsArray.length === 0) {
    console.log('[db.js] deleteBatchInboxTodos: ID数组为空，不执行任何操作。');
    return true; // 或者根据需要返回 false 或 0
  }
  console.log('[db.js] deleteBatchInboxTodos: 准备删除的ID:', todoIdsArray);
  try {
    const placeholders = todoIdsArray.map(() => '?').join(',');
    const stmt = db.prepare(`DELETE FROM todos WHERE id IN (${placeholders}) AND source = 'inbox'`);
    const info = stmt.run(...todoIdsArray);
    console.log(`[db.js] deleteBatchInboxTodos: ${info.changes} 条收件箱待办事项已删除。`);
    return info.changes > 0; // 返回是否至少删除了一条记录
  } catch (error) {
    console.error('[db.js] deleteBatchInboxTodos 失败:', error);
    return false;
  }
}

// 获取归档待办事项
function getArchivedTodos() {
  try {
    console.log('[db.js] 获取归档待办事项');
    const rows = db.prepare(`
      SELECT * FROM todos WHERE archived = 1
    `).all();
    
    console.log('[db.js] 从SQLite获取到的归档待办事项数量:', rows.length);
    return rows.map(row => formatTodoFromDb(row));
  } catch (error) {
    console.error('[db.js] 获取归档待办事项失败:', error);
    return [];
  }
}

// 归档待办事项
function archiveTodo(todo, originalSource) {
  try {
    console.log('[db.js] 归档待办事项:', todo.id, '来源:', originalSource);
    
    // 先检查待办事项是否存在
    const checkStmt = db.prepare(`SELECT * FROM todos WHERE id = ?`);
    const existingTodo = checkStmt.get(todo.id);
    
    if (!existingTodo) {
      console.error('[db.js] 要归档的待办事项不存在:', todo.id);
      return false;
    }
    
    console.log('[db.js] 待办事项存在，准备归档');
    
    // 更新待办事项为归档状态
    const stmt = db.prepare(`
      UPDATE todos SET
        archived = 1,
        archived_date = ?,
        original_source = ?
      WHERE id = ?
    `);
    
    const result = stmt.run(
      new Date().toISOString(),
      originalSource,
      todo.id
    );
    
    if (result.changes === 0) {
      console.error('[db.js] 归档待办事项失败，没有记录被更新:', todo.id);
      return false;
    }
    
    console.log('[db.js] 待办事项已成功归档:', todo.id, '更新的记录数:', result.changes);
    return true;
  } catch (error) {
    console.error('[db.js] 归档待办事项失败:', error);
    return false;
  }
}

// 批量归档待办事项
function archiveBatchTodos(todos, source) {
  try {
    console.log('[db.js] 批量归档待办事项:', todos.length, '个，来源:', source);
    
    // 检查所有待办事项是否都存在
    const checkStmt = db.prepare(`SELECT id FROM todos WHERE id = ?`);
    const missingTodos = todos.filter(todo => !checkStmt.get(todo.id));
    
    if (missingTodos.length > 0) {
      console.error('[db.js] 部分待办事项不存在:', missingTodos.map(t => t.id));
      // 但我们仍然继续处理存在的待办事项
    }
    
    const stmt = db.prepare(`
      UPDATE todos SET
        archived = 1,
        archived_date = ?,
        original_source = ?
      WHERE id = ?
    `);
    
    let updatedCount = 0;
    
    const archiveTodoTransaction = db.transaction((todoList) => {
      const now = new Date().toISOString();
      for (const todo of todoList) {
        const result = stmt.run(now, source, todo.id);
        updatedCount += result.changes;
      }
    });
    
    archiveTodoTransaction(todos);
    console.log('[db.js] 批量归档完成，成功更新', updatedCount, '个待办事项，总共:', todos.length);
    
    return updatedCount > 0;
  } catch (error) {
    console.error('[db.js] 批量归档待办事项失败:', error);
    return false;
  }
}

// 删除归档待办事项
function deleteArchivedTodo(todoId) {
  try {
    const stmt = db.prepare(`
      DELETE FROM todos WHERE id = ? AND archived = 1
    `);
    stmt.run(todoId);
    return true;
  } catch (error) {
    console.error('[db.js] 删除归档待办事项失败:', error);
    return false;
  }
}

// 批量删除归档待办事项
function deleteArchivedBatchTodos(todoIds) {
  try {
    const stmt = db.prepare(`
      DELETE FROM todos WHERE id = ? AND archived = 1
    `);
    
    const deleteTodo = db.transaction((ids) => {
      for (const id of ids) {
        stmt.run(id);
      }
    });
    
    deleteTodo(todoIds);
    return true;
  } catch (error) {
    console.error('[db.js] 批量删除归档待办事项失败:', error);
    return false;
  }
}

// 移动待办事项到指定日期
function moveTodoToDate(todoId, newDateKey) {
  try {
    const stmt = db.prepare(`
      UPDATE todos SET
        date_key = ?,
        source = 'date'
      WHERE id = ? AND archived = 0
    `);
    
    stmt.run(newDateKey, todoId);
    return true;
  } catch (error) {
    console.error('[db.js] 移动待办事项到日期失败:', error);
    return false;
  }
}

// 从数据库行格式化待办事项对象
function formatTodoFromDb(row) {
  if (!row) return null;
  return {
    ...row,
    completed: row.completed === 1,
    archived: row.archived === 1,
    priority: row.priority === null ? 0 : row.priority, // 处理可能的 null 值
    // date_key 可能为 null，保持原样
    // source 可能为 'inbox' 或 'date'
  };
}

// Helper function to parse reminder data from database row
// IMPORTANT: Place calculateNextTriggerTimestamp function definition BEFORE this line if it's not already there.
// For this edit, we assume it will be placed before _formatReminderFromDb by the overall edit structure.

function calculateNextTriggerTimestamp(reminder) {
  // 支持两种命名风格，优先使用下划线风格，这是数据库风格
  const start_date = reminder.start_date || reminder.startDate;
  const reminder_time = reminder.reminder_time || reminder.reminderTime;
  const recurrence_type = reminder.recurrence_type || reminder.recurrenceType;
  const recurrence_details = reminder.recurrence_details || reminder.recurrenceDetails;
  const id = reminder.id;
  
  if (!reminder_time) {
    console.error(`[DB-Calc] 提醒时间未提供，无法计算触发时间 (ID: ${id})`);
    return null;
  }
  
  let timeParts = reminder_time.split(':');
  const hours = parseInt(timeParts[0], 10);
  const minutes = parseInt(timeParts[1], 10);
  const seconds = 0; // 不再使用秒，固定为0
  
  if (!start_date) {
    console.error(`[DB-Calc] 起始日期未提供，无法计算触发时间 (ID: ${id})`);
    return null;
  }
  
  let [year, month, day] = start_date.split('-').map(Number);
  month -= 1;

  const baseStartDate = new Date(year, month, day, hours, minutes, seconds);
  let nextTrigger = new Date(baseStartDate.getTime());

  if (recurrence_type === 'ONCE') {
    return baseStartDate.getTime();
  }
  
  // 确保计算的 nextTrigger 不会早于 baseStartDate
  // 对于 DAILY, WEEKLY, MONTHLY_BY_DATE，如果 baseStartDate 本身就满足条件，它就是第一个触发点

  if (recurrence_type === 'DAILY') {
    // 第一个触发点就是 start_date 和 reminder_time 结合的时间
    return baseStartDate.getTime(); 
  }

  if (recurrence_type === 'WEEKLY') {
    if (!recurrence_details || !recurrence_details.daysOfWeek || recurrence_details.daysOfWeek.length === 0) {
      console.warn(`[DB-Calc] 每周提醒 (ID: ${id}) 缺少 daysOfWeek。`);
      return null;
    }
    const sortedDaysOfWeek = [...recurrence_details.daysOfWeek].sort((a, b) => a - b);
    nextTrigger.setHours(hours, minutes, seconds, 0);

    for (let i = 0; i < 7; i++) {
      if (nextTrigger.getTime() >= baseStartDate.getTime() && sortedDaysOfWeek.includes(nextTrigger.getDay())) {
        return nextTrigger.getTime();
      }
      nextTrigger.setDate(nextTrigger.getDate() + 1);
      nextTrigger.setHours(hours,minutes,seconds,0); // 确保下一天也是在正确的提醒时间
    }
    console.warn(`[DB-Calc] 每周提醒 (ID: ${id}) 从 start_date 开始7天内未找到有效触发。`);
    return null; // 应该不会发生，除非 start_date 周围7天都没有匹配
  }

  if (recurrence_type === 'MONTHLY_BY_DATE') {
    if (!recurrence_details || typeof recurrence_details.dayOfMonth !== 'number') {
      console.warn(`[DB-Calc] 每月按日期提醒 (ID: ${id}) 缺少 dayOfMonth。`);
      return null;
    }
    const targetDay = recurrence_details.dayOfMonth;
    nextTrigger = new Date(year, month, targetDay, hours, minutes, seconds, 0);

    // 如果计算出的日期早于 start_date (例如 start_date 是15号，targetDay 是10号)
    // 或者月份因为 targetDay 不存在而跳了 (例如 start_date 是2月15，targetDay是30)
    // 则需要尝试下个月
    while (nextTrigger.getTime() < baseStartDate.getTime() || 
           (nextTrigger.getMonth() !== month && nextTrigger.getFullYear() === year) || 
           (nextTrigger.getFullYear() > year && nextTrigger.getMonth() > 0 && targetDay < baseStartDate.getDate() )) { // 处理跨年且目标日在起始日之前的情况
        month++;
        if (month > 11) {
            month = 0;
            year++;
        }
        nextTrigger = new Date(year, month, targetDay, hours, minutes, seconds, 0);
        // 防止死循环
        if (year > baseStartDate.getFullYear() + 2) {
            console.warn(`[DB-Calc] MONTHLY_BY_DATE (ID: ${id}) 从 start_date 开始似乎无法找到未来有效触发点。`);
            return null;
        }
    }
    // 再次检查，确保日期是有效的（目标日存在于该月）
    if (nextTrigger.getDate() !== targetDay) {
        console.warn(`[DB-Calc] MONTHLY_BY_DATE (ID: ${id}) 目标日期 ${targetDay} 在计算月份 ${year}-${month+1} 中无效。`);
        return null; // 或者尝试找到下一个有效的这样的月份，但当前逻辑是直接失败
    }
    return nextTrigger.getTime();
  }

  if (recurrence_type === 'MONTHLY_BY_DAY_OF_WEEK') {
    if (!recurrence_details || typeof recurrence_details.weekOfMonth !== 'number' || typeof recurrence_details.dayOfWeek !== 'number') {
      console.warn(`[DB-Calc] 每月按星期几提醒 (ID: ${id}) 缺少 recurrence_details。`);
      return null;
    }
    
    let searchYear = year;
    let searchMonth = month;

    for(let i=0; i<24; i++) { // 尝试从 start_date 的月份开始，最多24个月
        let calculatedDate = getNthDayOfWeekInMonthForInitial(searchYear, searchMonth, recurrence_details.dayOfWeek, recurrence_details.weekOfMonth, hours, minutes, seconds);
        if (calculatedDate && calculatedDate.getTime() >= baseStartDate.getTime()) {
            return calculatedDate.getTime();
        }
        searchMonth++;
        if (searchMonth > 11) {
            searchMonth = 0;
            searchYear++;
        }
        // 如果 calculatedDate 存在但早于 baseStartDate，循环会继续到下个月
    }
    console.warn(`[DB-Calc] MONTHLY_BY_DAY_OF_WEEK (ID: ${id}) 未找到从 start_date 开始的有效触发点。`);
    return null;
  }

  console.warn(`[DB-Calc] 未知或不支持的重复类型: ${recurrence_type} (ID: ${id})`);
  return null;
}

// Calculate the next trigger timestamp AFTER a given trigger timestamp
function calculateNextTriggerAfter(currentTriggerTimestamp, reminder) {
  // 将前端驼峰命名风格的数据映射到期望的下划线风格
  const reminder_time = reminder.reminderTime || reminder.reminder_time;
  const recurrence_type = reminder.recurrenceType || reminder.recurrence_type;
  const recurrence_details = reminder.recurrenceDetails || reminder.recurrence_details;
  const id = reminder.id;
  const start_date = reminder.startDate || reminder.start_date;
  
  let timeParts = reminder_time.split(':');
  const hours = parseInt(timeParts[0], 10);
  const minutes = parseInt(timeParts[1], 10);
  const seconds = 0; // 不再使用秒，固定为0

  let lastTriggerDate = new Date(currentTriggerTimestamp);
  let nextTrigger = new Date(lastTriggerDate.getTime());


  if (recurrence_type === 'ONCE') {
    return null; 
  }

  if (recurrence_type === 'DAILY') {
    nextTrigger.setDate(lastTriggerDate.getDate() + 1);
    nextTrigger.setHours(hours, minutes, seconds, 0);
    return nextTrigger.getTime();
  }

  if (recurrence_type === 'WEEKLY') {
    if (!recurrence_details || !recurrence_details.daysOfWeek || recurrence_details.daysOfWeek.length === 0) {
      console.warn(`[DB-CalcNext] 每周提醒 (ID: ${id}) 缺少 daysOfWeek。`);
      return null;
    }

    // 转换为JS中的日期，0 = 周日, 1 = 周一, ..., 6 = 周六
    const sortedDaysOfWeek = [...recurrence_details.daysOfWeek].sort((a, b) => a - b);

    // 最简单方法：从当天开始每天检查，找到第一个匹配的天
    nextTrigger.setDate(lastTriggerDate.getDate() + 1); // 从下一天开始
    nextTrigger.setHours(hours, minutes, seconds, 0);

    // 最多循环7天即一周
    for (let i = 0; i < 7; i++) {
      if (sortedDaysOfWeek.includes(nextTrigger.getDay())) {
        return nextTrigger.getTime();
      }
      nextTrigger.setDate(nextTrigger.getDate() + 1);
    }

    console.warn(`[DB-CalcNext] 每周提醒 (ID: ${id}) 配置或计算出错，无法找到下一个匹配的星期几。`);
    return null;
  }

  if (recurrence_type === 'MONTHLY_BY_DATE') {
    if (!recurrence_details || typeof recurrence_details.dayOfMonth !== 'number') {
      console.warn(`[DB-CalcNext] 每月按日期提醒 (ID: ${id}) 缺少 dayOfMonth。`);
      return null;
    }

    const targetDay = recurrence_details.dayOfMonth;
    
    // 从上次触发的下个月开始尝试
    let newMonth = lastTriggerDate.getMonth() + 1;
    let newYear = lastTriggerDate.getFullYear();
    
    if (newMonth > 11) {
      newMonth = 0;
      newYear++;
    }
    
    // 创建下个月的目标日期
    let nextDate = new Date(newYear, newMonth, targetDay, hours, minutes, seconds, 0);
    
    // 处理目标日在某月不存在的情况（例如2月30日）
    // 如果日期超出范围，JS会自动调整到下个月
    if (nextDate.getMonth() !== newMonth) {
      // 这种情况下，使用该月的最后一天
      nextDate = new Date(newYear, newMonth + 1, 0, hours, minutes, seconds, 0);
    }
    
    return nextDate.getTime();
  }

  if (recurrence_type === 'MONTHLY_BY_DAY_OF_WEEK') {
    // 类似前面的实现，但需要计算"每月第N个星期X"
    // 这里是简化版，实际实现可能更复杂
    console.warn(`[DB-CalcNext] MONTHLY_BY_DAY_OF_WEEK (ID: ${id}) 在未来24个月内未找到有效触发点。`);
    return null;
  }

  console.warn(`[DB-CalcNext] 未知或不支持的重复类型: ${recurrence_type} (ID: ${id})`);
  return null;
}

// 将数据库行转换为提醒对象
function _formatReminderFromDb(row) {
  if (!row) return null;
  
  // 转换为驼峰命名风格，方便前端使用
  return {
    id: row.id,
    title: row.title,
    description: row.description,
    reminderTime: row.reminder_time,
    recurrenceType: row.recurrence_type,
    recurrenceDetails: row.recurrence_details ? JSON.parse(row.recurrence_details) : null,
    startDate: row.start_date,
    nextTriggerTimestamp: row.next_trigger_timestamp,
    isActive: row.is_active === 1,
    createdAt: row.created_at,
    updatedAt: row.updated_at
  };
}

// 创建提醒事项
function createReminder(reminder) {
  try {
    // 生成ID，确保即使前端不提供也能有唯一ID
    const id = reminder.id || Date.now().toString();
    const now = new Date().toISOString();
    
    // 将前端驼峰命名风格的数据映射到数据库下划线风格
    const reminderForCalculation = {
      id: id,
      start_date: reminder.startDate,
      reminder_time: reminder.reminderTime,
      recurrence_type: reminder.recurrenceType,
      recurrence_details: reminder.recurrenceDetails,
      is_active: reminder.isActive !== undefined ? reminder.isActive : true
    };

    console.log(`[db.js] 计算提醒时间 - ID: ${id}, 标题: ${reminder.title}, 时间: ${reminder.reminderTime}, 类型: ${reminder.recurrenceType}`);
    
    // 计算下一次触发时间
    const nextTriggerTimestamp = calculateNextTriggerTimestamp(reminderForCalculation);
    if (nextTriggerTimestamp === null) {
      console.error(`[db.js] 无法计算提醒的下一次触发时间 - ID: ${id}, 标题: ${reminder.title}, 开始日期: ${reminder.startDate}, 时间: ${reminder.reminderTime}`);
      return false;
    }

    console.log(`[db.js] 提醒计算结果 - ID: ${id}, 下次触发时间: ${new Date(nextTriggerTimestamp).toLocaleString()}`);
    
    // 插入提醒到数据库
    const stmt = db.prepare(`
      INSERT INTO reminders (
        id, title, description, reminder_time, 
        recurrence_type, recurrence_details, start_date, 
        next_trigger_timestamp, is_active, created_at, updated_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);
    
    stmt.run(
      id,
      reminder.title,
      reminder.description || null,
      reminder.reminderTime, // 驼峰转换为下划线
      reminder.recurrenceType, // 驼峰转换为下划线
      reminder.recurrenceDetails ? JSON.stringify(reminder.recurrenceDetails) : null,
      reminder.startDate, // 驼峰转换为下划线
      nextTriggerTimestamp, // Calculated value
      reminder.isActive !== undefined ? (reminder.isActive ? 1 : 0) : 1, // 驼峰转换为下划线
      now,
      now
    );
    
    // 返回创建的提醒事项对象，包括计算的下一次触发时间
    const createdReminder = getReminderById(id);
    if (createdReminder) {
      console.log(`[db.js] 成功创建提醒 - ID: ${id}, 标题: ${createdReminder.title}, 下次触发: ${new Date(createdReminder.nextTriggerTimestamp).toLocaleString()}`);
      return createdReminder;
    } else {
      console.error(`[db.js] 提醒已插入数据库但无法检索 - ID: ${id}`);
      return true; // 至少插入成功了
    }
  } catch (error) {
    console.error(`[db.js] 创建提醒事项失败: ${error.message}`, 'Data:', JSON.stringify({
      id: reminder.id,
      title: reminder.title,
      reminderTime: reminder.reminderTime,
      recurrenceType: reminder.recurrenceType,
      startDate: reminder.startDate
    }));
    return false;
  }
}

// 获取所有提醒事项
function getAllReminders() {
  try {
    const rows = db.prepare('SELECT * FROM reminders ORDER BY created_at DESC').all();
    return rows.map(_formatReminderFromDb);
  } catch (error) {
    console.error('[db.js] 获取所有提醒事项失败:', error);
    return [];
  }
}

// 根据ID获取提醒事项
function getReminderById(id) {
  try {
    const row = db.prepare('SELECT * FROM reminders WHERE id = ?').get(id);
    return _formatReminderFromDb(row);
  } catch (error) {
    console.error(`[db.js] 获取提醒事项 ${id} 失败:`, error);
    return null;
  }
}

// 更新提醒事项
function updateReminder(reminder) {
  try {
    const now = new Date().toISOString();
    
    // 将前端驼峰命名风格的数据映射到数据库下划线风格
    const reminderForCalculation = {
      id: reminder.id,
      start_date: reminder.startDate,
      reminder_time: reminder.reminderTime,
      recurrence_type: reminder.recurrenceType,
      recurrence_details: reminder.recurrenceDetails,
      is_active: reminder.isActive !== undefined ? reminder.isActive : true
    };

    const nextTriggerTimestamp = calculateNextTriggerTimestamp(reminderForCalculation);

    const stmt = db.prepare(`
      UPDATE reminders 
      SET 
        title = ?, 
        description = ?, 
        reminder_time = ?, 
        recurrence_type = ?, 
        recurrence_details = ?, 
        start_date = ?, 
        next_trigger_timestamp = ?,
        is_active = ?, 
        updated_at = ?
      WHERE id = ?
    `);
    const result = stmt.run(
      reminder.title,
      reminder.description || null,
      reminder.reminderTime,
      reminder.recurrenceType,
      reminder.recurrenceDetails ? JSON.stringify(reminder.recurrenceDetails) : null,
      reminder.startDate,
      nextTriggerTimestamp, // Calculated value
      reminder.isActive !== undefined ? (reminder.isActive ? 1 : 0) : 1,
      now,
      reminder.id
    );
    
    if (result.changes > 0) {
      // 返回更新后的提醒事项对象
      return getReminderById(reminder.id);
    }
    return false;
  } catch (error) {
    console.error('[db.js] 更新提醒事项失败:', error, 'Data:', reminder);
    return false;
  }
}

// 删除提醒事项
function deleteReminder(id) {
  try {
    const stmt = db.prepare('DELETE FROM reminders WHERE id = ?');
    const result = stmt.run(id);
    return result.changes > 0;
  } catch (error) {
    console.error(`[db.js] 删除提醒事项 ${id} 失败:`, error);
    return false;
  }
}

// 获取到期的活动提醒事项
function getDueReminders(currentTime) {
  try {
    if (!db) {
      console.error('[db.js] 数据库未初始化，无法获取到期提醒');
      return [];
    }
    
    // 查询活动状态且下次触发时间小于等于当前时间的提醒
    const stmt = db.prepare('SELECT * FROM reminders WHERE next_trigger_timestamp <= ? AND is_active = 1 ORDER BY next_trigger_timestamp ASC');
    
    // 以毫秒为单位的时间戳
    const rows = stmt.all(currentTime);
    
    if (rows.length > 0) {
      console.log(`[db.js] 找到 ${rows.length} 条到期提醒，当前时间: ${new Date(currentTime).toLocaleString()}`);
      rows.forEach(row => {
        console.log(`[db.js] 到期提醒: ${row.id}, 标题: ${row.title}, 触发时间: ${new Date(row.next_trigger_timestamp).toLocaleString()}`);
      });
    }
    
    return rows.map(_formatReminderFromDb);
  } catch (error) {
    console.error('[db.js] 获取到期提醒事项失败:', error);
    return [];
  }
}

// 更新提醒的下次触发时间和状态
function updateReminderNextTrigger(reminderId, nextTimestamp, isActive = 1) {
  try {
    const now = new Date().toISOString();
    const stmt = db.prepare(`
      UPDATE reminders
      SET next_trigger_timestamp = ?,
          is_active = ?,
          updated_at = ?
      WHERE id = ?
    `);
    // 如果 nextTimestamp 为 null (例如一次性提醒完成), is_active 通常应设为 0
    const finalIsActive = nextTimestamp === null ? 0 : isActive;
    const result = stmt.run(nextTimestamp, finalIsActive, now, reminderId);
    if (result.changes > 0) {
        console.log(`[db.js] Reminder ${reminderId} next trigger updated to ${nextTimestamp}, isActive: ${finalIsActive}`);
    } else {
        console.warn(`[db.js] No changes made when updating next trigger for reminder ${reminderId}. ID might not exist or values are the same.`);
    }
    return result.changes > 0;
  } catch (error) {
    console.error(`[db.js] 更新提醒 ${reminderId} 的下次触发时间失败:`, error);
    return false;
  }
}

// 从electron-store迁移数据到SQLite
function migrateFromElectronStore(data) {
  try {
    // 开始事务
    const transaction = db.transaction(() => {
      // 迁移设置
      // if (data.settings) {
      //   updateSettings(data.settings);
      // }
      
      // 迁移收件箱待办事项
      if (data.inboxTodos && Array.isArray(data.inboxTodos)) {
        for (const todo of data.inboxTodos) {
          addInboxTodo(todo);
        }
      }
      
      // 迁移按日期的待办事项
      if (data.todosByDate && typeof data.todosByDate === 'object') {
        for (const [dateKey, todos] of Object.entries(data.todosByDate)) {
          if (Array.isArray(todos)) {
            for (const todo of todos) {
              addTodoToDate(todo, dateKey);
            }
          }
        }
      }
      
      // 迁移归档待办事项
      if (data.archivedTodos && Array.isArray(data.archivedTodos)) {
        const stmt = db.prepare(`
          INSERT INTO todos (
            id, text, completed, created_at, completed_date, priority,
            archived, archived_date, original_source
          ) VALUES (?, ?, ?, ?, ?, ?, 1, ?, ?)\
        `);
        
        for (const todo of data.archivedTodos) {
          stmt.run(
            todo.id,
            todo.text,
            todo.completed ? 1 : 0,
            todo.createdAt,
            todo.completedDate || null,
            todo.priority || 0,
            todo.archivedDate,
            todo.originalSource
          );
        }
      }
    });
    
    // 执行事务
    transaction();
    
    return true;
  } catch (error) {
    console.error('[db.js] 从electron-store迁移数据失败:', error);
    return false;
  }
}

// 修改：加载初始数据时，只查询当天未归档的待办数量和收件箱未归档的待办数量
function loadAllData() {
  const today = new Date();
  const year = today.getFullYear();
  const month = (today.getMonth() + 1).toString().padStart(2, '0');
  const day = today.getDate().toString().padStart(2, '0');
  const todayDateKey = `${year}-${month}-${day}`;

  let todayUnarchivedUncompletedCount = 0;
  let inboxUnarchivedUncompletedCount = 0;

  try {
    // 查询当天未完成且未归档的待办数量
    const todayStmt = db.prepare(`
      SELECT COUNT(*) as count
      FROM todos
      WHERE date_key = ? AND completed = 0 AND archived = 0
    `);
    const todayResult = todayStmt.get(todayDateKey);
    todayUnarchivedUncompletedCount = todayResult ? todayResult.count : 0;

    // 查询收件箱中未完成且未归档的待办数量
    const inboxStmt = db.prepare(`
      SELECT COUNT(*) as count
      FROM todos
      WHERE source = 'inbox' AND completed = 0 AND archived = 0
    `);
    const inboxResult = inboxStmt.get();
    inboxUnarchivedUncompletedCount = inboxResult ? inboxResult.count : 0;

  } catch (error) {
    console.error('[db.js] 查询初始待办数量失败:', error);
    // 如果查询失败，返回0，避免应用崩溃
  }

  return {
    todayUnarchivedUncompletedCount,
    inboxUnarchivedUncompletedCount,
    // 初始加载时，这些字段返回为空或表示未加载的状态，而不是全部数据
    todosByDate: {}, // 或者可以考虑加载当天的todos，如果UI需要立即显示
    inboxTodos: [],
    archivedTodos: [] // 归档数据明确不在初始加载
  };
}

module.exports = {
  initDatabase,
  closeDatabase,
  checkConnection,
  insertDefaultSettings,
  getSettings,
  updateSettings,
  addTodo,
  updateTodo,
  deleteTodo,
  getTodosByDate,
  getTodosForDateKey,
  addTodoToDate,
  deleteTodoFromDate,
  deleteBatchTodosFromDate,
  getInboxTodos,
  addInboxTodo,
  deleteInboxTodo,
  deleteBatchInboxTodos,
  getArchivedTodos,
  archiveTodo,
  archiveBatchTodos,
  deleteArchivedTodo,
  deleteArchivedBatchTodos,
  moveTodoToDate,
  formatTodoFromDb,
  createReminder,
  getAllReminders,
  getReminderById,
  updateReminder,
  deleteReminder,
  getDueReminders,
  calculateNextTriggerTimestamp,
  calculateNextTriggerAfter,
  migrateFromElectronStore,
  loadAllData,
  getNthDayOfWeekInMonthForInitial,
  _formatReminderFromDb,
  updateReminderNextTrigger
}; 